package org.lab.tracecash.io;

import java.io.InputStream;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.apache.commons.lang.Validate;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.lab.tracecash.model.Account;
import org.lab.tracecash.model.Transaction;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

public abstract class BaseExcelImporter implements TransactionImporter {

	private static final Logger LOG = LoggerFactory.getLogger(BaseExcelImporter.class);

	protected abstract Integer getSheetNumber();

	protected abstract Integer getRowStart();

	protected abstract Integer getValueDateColumn();

	protected abstract Integer getDescriptionDateColumn();

	protected abstract Integer getAmountColumn();

	protected abstract Integer getMetadataStart();

	protected abstract Integer getMetadataEnd();

	protected abstract Integer getAmountScale();

	@Override
	public List<Transaction> parse(Account source, Account target, InputStream in) {
		Validate.notNull(in, "Missing InputStream");
		try {
			Workbook wb = WorkbookFactory.create(in);
			Sheet sheet = wb.getSheetAt(getSheetNumber());
			List<Transaction> list = new ArrayList<Transaction>();
			Integer row = getRowStart();
			while (true) {
				Row currentRow = sheet.getRow(row);
				if (currentRow == null) {
					break;
				} else {
					try {
						Date valueDate = getValueDate(currentRow);
						String desc = getDescription(currentRow);
						BigDecimal amount = getAmount(currentRow);
						Transaction operation = new Transaction();
						operation.setDesc(desc);
						operation.setValueDate(valueDate);
						operation.setAmount(amount);
						StringBuffer longDesc = new StringBuffer(desc);
						for (int i = getMetadataStart(); i < getMetadataEnd(); i++) {
							String str = readCellAsString(currentRow.getCell(i));
							if (str != null && !str.isEmpty()) {
								longDesc.append(str).append("|");
							}
						}
						operation.setLongDesc(longDesc.toString());
						if (amount.compareTo(BigDecimal.ZERO) > 0) {
							operation.setFrom(source);
							operation.setTo(target);
						} else {
							operation.setFrom(target);
							operation.setTo(source);
						}
						list.add(operation);
					} catch (Exception ex) {
						LOG.error(ex.getMessage());
					}
					row++;
				}
			}
			return list;
		} catch (Exception ex) {
			throw new RuntimeException("Error al leer el excel de movimientos: " + ex.getMessage(), ex);
		}
	}

	private Date getValueDate(Row row) {
		try {
			return readCellAsDate(row.getCell(getValueDateColumn()));
		} catch (Exception ex) {
			throw new RuntimeException("Invalid valueDate. " + ex.getMessage(), ex);
		}
	}

	private String getDescription(Row row) {
		try {
			return readCellAsString(row.getCell(getDescriptionDateColumn()));
		} catch (Exception ex) {
			throw new RuntimeException("Invalid description. " + ex.getMessage(), ex);
		}
	}

	private BigDecimal getAmount(Row row) {
		try {
			return readCellAsBigDecimal(row.getCell(getAmountColumn()));
		} catch (Exception ex) {
			throw new RuntimeException("Invalid amount. " + ex.getMessage(), ex);
		}
	}

	private Date readCellAsDate(Cell cell) {
		if (cell != null) {
			try {
				return cell.getDateCellValue();
			} catch (Exception ex) {
				ex.printStackTrace();
			}

			switch (cell.getCellType()) {
			case Cell.CELL_TYPE_STRING:
				try {
					return new SimpleDateFormat("dd/MM/yyyy").parse(cell.getStringCellValue());
				} catch (ParseException ex) {
					throw new RuntimeException("Unsupported format: " + cell.getStringCellValue() + ". Expected: dd/MM/yyyyy");
				}
			default:
				throw new RuntimeException("Unsupported cell type: " + cell.getCellType());
			}
		} else {
			return null;
		}
	}

	private String readCellAsString(Cell cell) {
		if (cell != null) {
			return cell.toString();
		} else {
			return null;
		}
	}

	private BigDecimal readCellAsBigDecimal(Cell cell) {
		BigDecimal result = null;
		if (cell != null) {
			switch (cell.getCellType()) {
			case Cell.CELL_TYPE_NUMERIC:
				try {
					result = new BigDecimal(cell.getNumericCellValue());
					break;
				} catch (Exception ex) {
					throw new RuntimeException("Invalid cell number value");
				}
			case Cell.CELL_TYPE_STRING:
				try {
					result = new BigDecimal(cell.getStringCellValue());
					break;
				} catch (Exception ex) {
					throw new RuntimeException("Unsupported numberformat: " + cell.getStringCellValue());
				}
			default:
				throw new RuntimeException("Unsupported cell type: " + cell.getCellType());
			}
		}
		if (result != null && result.scale() > getAmountScale()) {
			LOG.trace("Invalid scale: " + result);
			result = result.setScale(getAmountScale(), RoundingMode.HALF_EVEN);
		}
		return result;
	}
}
